# -*- coding: utf-8 -*-
# Part of Odoo. See LICENSE file for full copyright and licensing details.

from odoo import fields, models, api, tools

class DictLearning(models.Model):
    _description = 'DictWordLearning'
    _name = "dict.learning"
    _order = "level desc, word_id"

    word_id = fields.Many2one('dict.word', 'word_id', index=True, required=True )
    raw_word_id = fields.Many2one('dict.raw_word', related='word_id.raw_word_id' )
    word = fields.Char( related='word_id.word' )
    # word_name = fields.Char( related='word_id.name' )
    number = fields.Integer( related='word_id.number' )
    name = fields.Char( related='word_id.name' )
    is_key = fields.Boolean( related='word_id.is_key')
    part_of_speech_ids = fields.Many2many( related='word_id.part_of_speech_ids' )
    part_of_speech = fields.Char( related='word_id.part_of_speech' )
    meanings_cn = fields.Char(related='word_id.meanings_cn' )

    user_id = fields.Many2one('res.users', 'user_id', index=True, required=True )

    # sort = fields.Integer(default=0, index=True)
    level = fields.Integer(default=0, index=True)

    @api.model
    def groupby_level(self):
        query = """
                with var as ( 
                    select 6 user_id, 2 admin_id 
                ), admin_word as (
                    select w.id, w.raw_word_id,
                    (case when al.level is null then 0 else al.level end ) as level
                    from dict_word w
                    left join dict_learning al 
                    on w.id = al.word_id and user_id in (select admin_id from var)
                ), user_word as (
                    select distinct
                    (case when ul.level is null then w.level else ul.level end ) as level,
                    w.raw_word_id
                    from admin_word w
                    left join dict_learning ul 
                    on w.id = ul.word_id and user_id in (select user_id from var)
                ), words as (
                    select level, raw_word_id, rw.name from user_word uw, dict_raw_word rw
                    where uw.raw_word_id = rw.id
                    order by level desc, lower(rw.name)
                )
                
                select * from words
                
        """

        user_id = self.env.user.id
        admin_id = self.env['ir.model.data'].xmlid_to_res_id('base.user_admin')

        self.env.cr.execute(query, ( user_id , admin_id ))
        res = {}
        for val in self.env.cr.dictfetchall():
            level = val['level']
            raw_word_id = val['raw_word_id']
            if not res.get(level):
                res[level] = {
                    'level': level,
                    'raw_word_ids': [],
                }

            res[level]['raw_word_ids'].append(raw_word_id)

        res0 = res.get(0)

        if res0:
            del res[0]

        keys = list( res.keys() )
        keys.sort(reverse=True)

        return ( res0 and [res0] or [] ) + [ res[key] for key in keys ]

